﻿'Created By : Ahmad Abdan
'Date : 09/06/2010
'Subject : DBConnection Class 

Imports System.Data.SqlClient
Imports System.Data
Imports System.Configuration
Imports System.Data.OleDb
Imports System.Data.Odbc

Public Class DBConnectionClass

    Public strOracleConnection As String = ConfigurationManager.ConnectionStrings("OracleConnectionString").ConnectionString.ToString
    'Private strConnection As String = System.Configuration.ConfigurationSettings.AppSettings("RTSDatabaseConnectionString")
    Private strConnection As String = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ConnectionString.ToString
    Private objSqlConnection As SqlConnection
    Private objSqlCommand As New SqlCommand
    Private objOracleConnection As OdbcConnection
    Private objOledbCommand As New OdbcCommand

    Private nCounter As Integer
    Dim GFunction As New GlobalFunctions

    Public Sub New()
        objSqlConnection = New SqlClient.SqlConnection(strConnection)
        objOracleConnection = New OdbcConnection(strOracleConnection)
    End Sub

    Public Function ExecuteNonQuery(ByRef DBFieldsArrayList As ArrayList, ByVal storedProcedureName As String) As Integer
        Dim noOfEffectedRows As Integer

        Try
            objSqlConnection.Open()

            objSqlCommand = New SqlCommand(storedProcedureName, objSqlConnection)
            objSqlCommand.CommandType = CommandType.StoredProcedure

            fillSqlCommandParametersArray(DBFieldsArrayList)

            noOfEffectedRows = objSqlCommand.ExecuteNonQuery()

        Catch ex As SqlException
            noOfEffectedRows = Nothing
            Throw ex
            'MsgBox(ex.Message)
            ' System.Console.Write("ss")
        Catch ex As Exception
            Throw ex
            noOfEffectedRows = Nothing

        Finally
            objSqlConnection.Close()
        End Try
        Return noOfEffectedRows

    End Function

    Public Function ExecuteScalar(ByRef DBFieldsArrayList As ArrayList, ByVal storedProcedureName As String) As Object
        Dim Scalar As Object
        Try
            objSqlConnection.Open()

            objSqlCommand = New SqlCommand(storedProcedureName, objSqlConnection)
            objSqlCommand.CommandType = CommandType.StoredProcedure

            fillSqlCommandParametersArray(DBFieldsArrayList)

            Scalar = objSqlCommand.ExecuteScalar()

        Catch ex As Exception
            Scalar = Nothing
        Finally
            objSqlConnection.Close()
        End Try
        Return Scalar

    End Function

    Public Function ExecuteReader(ByRef DBFieldsArrayList As ArrayList, ByVal storedProcedureName As String) As SqlDataReader
        Dim objSqlDataReader As SqlDataReader
        Try
            objSqlConnection.Open()

            objSqlCommand = New SqlCommand(storedProcedureName, objSqlConnection)
            objSqlCommand.CommandType = CommandType.StoredProcedure


            fillSqlCommandParametersArray(DBFieldsArrayList)

            objSqlDataReader = objSqlCommand.ExecuteReader(CommandBehavior.CloseConnection)

        Catch ex As Exception
            objSqlDataReader = Nothing
            objSqlConnection.Close()
        Finally
            'objSqlConnection.Close()
        End Try
        Return objSqlDataReader
    End Function

    Public Function FillDataset(ByRef DBFieldsArrayList As ArrayList, ByVal storedProcedureName As String) As DataSet
        Dim objSqlDataAdapter As New SqlDataAdapter(objSqlCommand)
        Dim selectedDatatset As New DataSet
        Try
            objSqlConnection.Open()

            objSqlCommand = New SqlCommand(storedProcedureName, objSqlConnection)
            objSqlCommand.CommandTimeout = objSqlCommand.CommandTimeout * 3
            objSqlCommand.CommandType = CommandType.StoredProcedure

            fillSqlCommandParametersArray(DBFieldsArrayList)

            objSqlDataAdapter.SelectCommand = objSqlCommand

            objSqlDataAdapter.Fill(selectedDatatset)
        Catch ex As Exception
            selectedDatatset = Nothing
        Finally
            objSqlConnection.Close()
        End Try

        Return selectedDatatset
    End Function



    Public Function FillOracleDataset(ByVal ViewName As String, ByVal Condition As String) As DataSet

        Dim objOledbDataAdapter As New OdbcDataAdapter(objOledbCommand)
        Dim selectedDatatset As New DataSet
        Try

            objOracleConnection.Open()
            Dim state As String = objOracleConnection.State.ToString

            Dim command As String = "Select * from " & ViewName
            If Not (Condition Is Nothing) And Not (Condition = "") Then
                command &= " where " & Condition
            End If

            'Dim command As String = "select * from tab"

            objOledbCommand = New OdbcCommand(command, objOracleConnection)
            'objOracleConnection.
            objOledbCommand.CommandTimeout = objOracleConnection.ConnectionTimeout * 50000000
            objOledbCommand.CommandType = CommandType.Text

            objOledbDataAdapter.SelectCommand = objOledbCommand

            objOledbDataAdapter.Fill(selectedDatatset, "apps.xxrg_rts_oe_view")

        Catch ex As Exception
            selectedDatatset = Nothing
        Finally
            objOracleConnection.Close()
        End Try


        'Dim ds As New DataSet
        'ds = conn.ExecuteSQL("dbcc useroptions")
        'x = ds.Tables(0).Rows(12)("Value")

        Return selectedDatatset
    End Function



    Public Function ExecuteXmlReader(ByRef DBFieldsArrayList As ArrayList, ByVal storedProcedureName As String) As System.Xml.XmlReader
        Dim objXmlReader As System.Xml.XmlReader
        Try
            objSqlConnection.Open()

            objSqlCommand = New SqlCommand(storedProcedureName, objSqlConnection)
            objSqlCommand.CommandType = CommandType.StoredProcedure


            fillSqlCommandParametersArray(DBFieldsArrayList)

            objXmlReader = objSqlCommand.ExecuteXmlReader()

        Catch ex As Exception
            objXmlReader = Nothing
        Finally
            objSqlConnection.Close()
        End Try
        Return objXmlReader
    End Function

    Private Sub fillSqlCommandParametersArray(ByRef DBFieldsArrayList As ArrayList)

        objSqlCommand.Parameters.Clear()
        For nCounter = 0 To DBFieldsArrayList.Count - 1
            Dim objParameter As New SqlParameter
            Dim DBTableField As TableField = CType(DBFieldsArrayList(nCounter), TableField)
            objParameter.ParameterName = "@" + DBTableField.Name
            objParameter.Direction = ParameterDirection.Input

            'Check if the type is date to do some sort of conversion
            If DBTableField.Type = SqlDbType.DateTime Then
                objParameter.Value = GFunction.FormatDBDate(DBTableField.Value)
                objParameter.SqlDbType = SqlDbType.VarChar
            Else
                objParameter.Value = DBTableField.Value
                objParameter.SqlDbType = DBTableField.Type
            End If

            objSqlCommand.Parameters.Add(objParameter)
        Next

    End Sub

    Public ReadOnly Property ConnectionString() As String
        Get
            Return strConnection
        End Get
    End Property

    Public ReadOnly Property DBSqlConnection() As SqlConnection
        Get
            Return objSqlConnection
        End Get
    End Property

    


    

End Class
